﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;

namespace ufolder
{
    public class Edge
    {
        private int _id;
        private int _startvertex;
        private int _endvertex;

        public int Id
        {
            get
            {
                return this._id;
            }
        }

        public Edge(int id,int startvertex, int endvertex)
        {
            this._id = id; this._startvertex = startvertex; this._endvertex = endvertex;
        }

        public static Edge Create(SQLiteConnection c, int startvertex, int endvertex)
        {
            string sql = "INSERT INTO edges (startvertex, endVertex, hops) VALUES (" + startvertex + ", " + endvertex + ", 0);UPDATE edges SET entryedgeid=last_insert_rowid(), directedgeid=last_insert_rowid(), exitedgeid=last_insert_rowid() WHERE id=last_insert_rowid();";
                SQLiteCommand command = new SQLiteCommand(sql, c);
            try
            {
                command.Transaction = c.BeginTransaction();
                command.ExecuteNonQuery();

                command.CommandText = "SELECT last_insert_rowid() id";
                string id = command.ExecuteScalar().ToString();

                command.CommandText = "INSERT INTO edges (entryedgeid, directedgeid, exitedgeid, startvertex, endvertex, hops) SELECT id, " + id + ", " + id + ", StartVertex, " + endvertex + ", Hops + 1 FROM edges  WHERE endVertex = " + startvertex + ";";
                command.ExecuteNonQuery();

                command.CommandText = "INSERT INTO edges (entryedgeid, directedgeid, exitedgeid, startvertex, endvertex, hops) SELECT " + id + ", " + id + ", id, " + startvertex + ", EndVertex, Hops + 1 FROM edges WHERE StartVertex = " + endvertex + ";";
                command.ExecuteNonQuery();

                command.CommandText = "INSERT INTO edges (entryedgeid, directedgeid, exitedgeid, startvertex, endVertex, hops) SELECT A.Id, " + id + ", B.Id, A.StartVertex, B.EndVertex, A.Hops + B.Hops + 1 FROM edges A CROSS JOIN edges B WHERE A.EndVertex = " + startvertex + " AND B.StartVertex = " + endvertex + ";";
                command.ExecuteNonQuery();

                command.Transaction.Commit();

                return new Edge(int.Parse(id), startvertex, endvertex);
            }
            catch (SQLiteException ex)
            {
                command.Transaction.Rollback();
                System.Windows.Forms.MessageBox.Show(ex.Message, "Erreur", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
                return null;
            }
        }

        public static void Delete(SQLiteConnection c, int startvertex, int endvertex)
        {
            int id;
            string sql = "SELECT id FROM edges WHERE startvertex = " + startvertex + " AND endvertex = " + endvertex + " AND hops = 0 ";
            SQLiteCommand command = new SQLiteCommand(sql, c);
            try
            {
                command.Transaction = c.BeginTransaction();
                object ido = command.ExecuteScalar();

                if (ido == null) throw new SQLiteException("Edge direct inexistant");
                else id = int.Parse(ido.ToString());

                command.CommandText = "CREATE TEMP TABLE purgelist (id int); INSERT INTO purgelist SELECT id FROM edges WHERE directedgeid = " + id + ";";
                command.ExecuteNonQuery();

                command.CommandText = "INSERT INTO purgelist SELECT id FROM edges WHERE Hops > 0  AND ( entryedgeid IN ( SELECT id FROM purgelist ) OR exitedgeid IN ( SELECT id FROM purgelist ) ) AND id NOT IN (SELECT Id FROM purgelist )";

                int r = 0;
                do
                {
                    r = command.ExecuteNonQuery();
                } while (r != 0);

                command.CommandText = "DELETE FROM edges WHERE id IN ( SELECT id FROM purgelist); DROP TABLE purgelist;";
                command.ExecuteNonQuery();
                command.Transaction.Commit();
            }
            catch (SQLiteException ex)
            {
                command.Transaction.Rollback();
                System.Windows.Forms.MessageBox.Show(ex.Message, "Erreur", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
            }
        }
    }
}
